package edu.api.jdbc_api;

import java.sql.*;

public class Demo2 {

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        test4();
    }

    /**
     * 一对一的查询
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static void test4() throws ClassNotFoundException, SQLException {
        //2. 注册驱动,将驱动实例注册到DriverManager
        Class.forName("com.mysql.cj.jdbc.Driver");
        /**
         * 3. 获取数据库连接
         * url: 协议://ip:port/路劲?参数
         */
        String url = "jdbc:mysql://127.0.0.1:3306/book_shop?characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
        String user = "root";
        String password = "qwer1234";
        Connection connection = DriverManager.getConnection(url, user, password);

        String sql = "select bu.id u_id, username, password, email, phone, company,\n" +
                "       bs_address.id, user_id, area, detail_address, email_code, reciver, tel, is_default\n" +
                "from bs_address left join bs_user bu\n" +
                "    on bu.id = bs_address.user_id\n" +
                "where bs_address.id = 1";
        Statement statement = connection.createStatement();
        //查询返回结果集
        ResultSet set = statement.executeQuery(sql);
        //查询结果可能是多条时，用集合封装
        Bs_address bs_address = null;
        //遍历结果集
        while (set.next()) {
            //根据字段的位置获取对应字段的数据
            int u_id = set.getInt("u_id");
            String username = set.getString("username");
            String psw = set.getString("password");
            String email = set.getString("email");
            String company = set.getString("company");
            String phone = set.getString("phone");
            Bs_user bs_user = new Bs_user(u_id, username, psw, email, phone, company);
            int id = set.getInt("id");
            int user_id = set.getInt("user_id");
            String area = set.getString("area");
            String detail_address = set.getString("detail_address");
            String email_code = set.getString("email_code");
            String reciver = set.getString("reciver");
            String tel = set.getString("tel");
            String is_default = set.getString("is_default");
            bs_address = new Bs_address(id,user_id,area,detail_address,email_code,reciver,tel,is_default,bs_user);
        }
        System.out.println("bs_address = " + bs_address);
        set.close();
        statement.close();
        connection.close();
    }

    /**
     * 一对多的查询  自己尝试实现
     */


}
